﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DAL
{
    public class Lop
    {
        private DataSet _ds;
        //private const String SConn = "Data Source=DUCLINH-PC;Initial Catalog=HOUSMS;Persist Security Info=True;User ID=sa;Password=minhduc";
        private static string SConn = ConfigurationManager.ConnectionStrings["DucLM"].ConnectionString;
        public DataSet GetDataSet()
        {
            var conn = new SqlConnection(SConn);
            var cmd = new SqlCommand("LoadLop", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            var da = new SqlDataAdapter(cmd);
            var ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

        #region "Thêm mới lớp"
        public int Add(string tenLop, int ky)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = new SqlCommand("AddLop", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Parameters.AddWithValue("@TenLop", tenLop);
                cmd.Parameters.AddWithValue("@Ky", ky);
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region "Cập nhật thông tin lớp"
        public int Update(string tenLop, int ky)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = new SqlCommand("UpdateLop", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Parameters.AddWithValue("@TenLop", tenLop);
                cmd.Parameters.AddWithValue("@Ky", ky);
                return cmd.ExecuteNonQuery();
            }
            finally 
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region "Tìm kiếm theo tên lớp"
        public DataSet SearchByTenLop(string tenLop)
        {
            var ds = new DataSet();
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("SearchByTenLop", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("@TenLop", tenLop);
            da.Fill(ds, "SearchByTenLop");
            return ds;
        }
        #endregion

        #region "Tìm kiếm theo kỳ"
        public DataSet SearchByKy(int ky)
        {
            var ds = new DataSet();
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("SearchByKy", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("@Ky", ky);
            da.Fill(ds, "SearchByKy");
            return ds;
        }
        #endregion
    }
}
